import relationalStore from '@ohos.data.relationalStore';
import { Note } from '../model/Note';
import common from '@ohos.app.ability.common';

const DB_NAME = 'EducationNotes.db';
const TBL_USER = 'user';
const TBL_NOTES = 'notes';
const DB_VERSION = 2; // 数据库版本

// SQL语句


const TBL_CREATE_USER_SQL = `
  CREATE TABLE IF NOT EXISTS ${TBL_USER} (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_image TEXT,
    user_id TEXT NOT NULL,
    keywords TEXT NOT NULL,
    create_time INTEGER NOT NULL
  );
`;
const TBL_CREATE_SQL = `
  CREATE TABLE IF NOT EXISTS ${TBL_NOTES} (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT,
    create_time INTEGER NOT NULL,
    update_time INTEGER NOT NULL
  );
`;

class DatabaseHelper {
  private rdbStore: relationalStore.RdbStore | null = null;

  // 初始化数据库
  async initDB(context: common.UIAbilityContext): Promise<void> {
    if (this.rdbStore) {
      console.info('Database already initialized.');
      return;
    }
    const storeConfig: relationalStore.StoreConfig = {
      name: DB_NAME,
      securityLevel: relationalStore.SecurityLevel.S1
    };
    try {
      this.rdbStore = await relationalStore.getRdbStore(context, storeConfig);
      await this.rdbStore.executeSql(TBL_CREATE_SQL);
      await this.updateDB(context); // 调用更新方法
      console.info('Database initialized and table created successfully.');
    } catch (e) {
      console.error('Failed to init database', JSON.stringify(e));
    }
  }


  async updateDB(context: common.UIAbilityContext): Promise<void> {
    if (!this.rdbStore) {
      console.error('Database not initialized.');
      return;
    }
    try {
      // 这里假设数据库从版本 1 升级到版本 2
      await this.rdbStore.executeSql(TBL_CREATE_USER_SQL);
      console.info('Database updated and user table created successfully.');
    } catch (e) {
      console.error('Failed to update database', JSON.stringify(e));
    }
  }



  // 检查用户是否存在
  async isUserExist(userId: string): Promise<boolean> {
    if (!this.rdbStore) {
      throw new Error('Database not initialized');
    }

    const predicates = new relationalStore.RdbPredicates(TBL_USER);
    predicates.equalTo('user_id', userId);

    try {
      const resultSet = await this.rdbStore.query(predicates);
      const exists = resultSet.rowCount > 0;
      resultSet.close();
      return exists;
    } catch (e) {
      console.error('User existence check failed:', JSON.stringify(e));
      throw e;
    }
  }


  // 添加一条新用户,若已存在用户则为更新其信息
  async addOrUpdateUser(userImage: string, userId: string, keywords: string): Promise<number> {
    const valueBucket: relationalStore.ValuesBucket = {
      user_image: userImage,
      user_id: userId,
      keywords: keywords,
      create_time: Date.now()
    };
    const predicates = new relationalStore.RdbPredicates(TBL_USER);
    predicates.equalTo('user_id', userId);
    const exists = await this.rdbStore.query(predicates).then(resultSet => resultSet.rowCount > 0);
    if (exists) {
      const updateResult = await this.rdbStore.update(valueBucket, predicates);
      console.log(`User updated, affected rows: ${updateResult}`);
      return updateResult;
    } else {
      return await this.rdbStore.insert(TBL_USER, valueBucket);
    }
  }

  // 删除用户信息
  async deleteUser(userId: string): Promise<number> {
    const predicates = new relationalStore.RdbPredicates(TBL_USER);
    predicates.equalTo('user_id', userId);
    return await this.rdbStore.delete(predicates);
  }


  // 验证用户登录
  async validateUser(userId: string, keywords: string): Promise<boolean> {
    const predicates = new relationalStore.RdbPredicates(TBL_USER);
    predicates.equalTo('user_id', userId);
    predicates.equalTo('keywords', keywords);
    const resultSet = await this.rdbStore.query(predicates);
    const exists = resultSet.rowCount > 0;
    resultSet.close();
    return exists;
  }



  // 添加一条新笔记
  async addNote(note: Note): Promise<number> {
    const valueBucket: relationalStore.ValuesBucket = {
      title: note.title,
      content: note.content,
      create_time: note.create_time,
      update_time: note.update_time
    };
    return await this.rdbStore.insert(TBL_NOTES, valueBucket);
  }

  // 更新一条笔记
  async updateNote(note: Note): Promise<number> {
    const valueBucket: relationalStore.ValuesBucket = {
      title: note.title,
      content: note.content,
      update_time: Date.now() // 总是更新修改时间
    };
    const predicates = new relationalStore.RdbPredicates(TBL_NOTES);
    predicates.equalTo('id', note.id);
    return await this.rdbStore.update(valueBucket, predicates);
  }

  // 根据ID删除笔记
  async deleteNoteById(id: number): Promise<number> {
    const predicates = new relationalStore.RdbPredicates(TBL_NOTES);
    predicates.equalTo('id', id);
    return await this.rdbStore.delete(predicates);
  }

  // 查询所有笔记
  async queryAllNotes(): Promise<Note[]> {
    const predicates = new relationalStore.RdbPredicates(TBL_NOTES);
    predicates.orderByDesc('update_time'); // 按更新时间倒序排列
    const resultSet = await this.rdbStore.query(predicates, ['id', 'title', 'content', 'create_time', 'update_time']);
    const notes: Note[] = [];
    if (resultSet.rowCount > 0) {
      while (!resultSet.isAtLastRow) {
        resultSet.goToNextRow();
        const id = resultSet.getLong(resultSet.getColumnIndex('id'));
        const title = resultSet.getString(resultSet.getColumnIndex('title'));
        const content = resultSet.getString(resultSet.getColumnIndex('content'));
        const create_time = resultSet.getLong(resultSet.getColumnIndex('create_time'));
        const update_time = resultSet.getLong(resultSet.getColumnIndex('update_time'));
        notes.push(new Note(title, content, id, create_time, update_time));
      }
    }
    resultSet.close();
    return notes;
  }

  // 根据ID查询单条笔记
  async queryNoteById(id: number): Promise<Note | null> {
    const predicates = new relationalStore.RdbPredicates(TBL_NOTES);
    predicates.equalTo('id', id);
    const resultSet = await this.rdbStore.query(predicates);
    if (resultSet.rowCount === 0) {
      return null;
    }
    resultSet.goToFirstRow();
    const noteId = resultSet.getLong(resultSet.getColumnIndex('id'));
    const title = resultSet.getString(resultSet.getColumnIndex('title'));
    const content = resultSet.getString(resultSet.getColumnIndex('content'));
    const create_time = resultSet.getLong(resultSet.getColumnIndex('create_time'));
    const update_time = resultSet.getLong(resultSet.getColumnIndex('update_time'));
    resultSet.close();
    return new Note(title, content, noteId, create_time, update_time);
  }
}

// 使用单例模式，确保应用中只有一个DatabaseHelper实例
export const dbHelper = new DatabaseHelper();